USE [IUDICIUM]
GO

IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[usp_CONSULTA_BuscarServicio]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[usp_CONSULTA_BuscarServicio]
GO

SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

-- =============================================
-- Author:		Jose Carlos Gutierrez
-- Create date: 23/06/2012
-- Description:	Consulta si un si un servicio corresponde a los parametros enviados
-- =============================================
CREATE PROCEDURE [dbo].[usp_CONSULTA_BuscarServicio]
	@varCodigoServicio	VARCHAR(20),
	@varDocumento		VARCHAR(50)
AS
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;
	
    SELECT s.[servicioId]
      ,s.[tipoServicio]
	FROM [dbo].[tbl_Servicio] s
		JOIN [dbo].[tbl_Involucrado] i ON i.[servicioId] = s.[servicioId]
		JOIN 
			(
				SELECT [personaNaturalId] AS [personaId],
					[cedula] AS [documento]
				FROM [dbo].[tbl_PersonaNatural] pn JOIN 
					[tbl_Persona] p ON p.[personaId] = pn.[personaNaturalId]
				UNION
				SELECT [personaJuridicaId] AS [personaId],
					[nit] AS [documento]
				FROM [dbo].[tbl_PersonaJuridica] pj JOIN 
					[tbl_Persona] p ON p.[personaId] = pj.[personaJuridicaId]
			) persona ON persona.[personaId] = i.[personaId]
	WHERE REPLACE([codigoServicio],' ','') = REPLACE(@varCodigoServicio,' ','')
		AND persona.[documento] = @varDocumento
END

GO


DELETE FROM [dbo].[tbl_Version]
GO

INSERT INTO [dbo].[tbl_Version]
           ([versionMayor]
           ,[versionMenor]
           ,[release])
     VALUES
           (1
           ,3
           ,1)
GO